"""
系统数据库管理器

负责系统数据库的创建、初始化和管理
"""

import os
import sys
from typing import Dict, List, Any

# 添加UserManagement路径
current_dir = os.path.dirname(os.path.abspath(__file__))
parent_dir = os.path.dirname(current_dir)
user_management_dir = os.path.join(parent_dir, "UserManagement")
if user_management_dir not in sys.path:
    sys.path.insert(0, user_management_dir)

from database_manager import DatabaseManager
from database_instance import DatabaseInstance
from storage_engine import CachePolicy
from table_schema import Column, ColumnType
from statistics import Logger

# 导入UserManagement组件
try:
    from system_schema import SYSTEM_DATABASE_NAME, SYSTEM_TABLES, get_all_system_tables
except ImportError:
    # 如果相对导入失败，尝试绝对导入
    sys.path.append(user_management_dir)
    from system_schema import SYSTEM_DATABASE_NAME, SYSTEM_TABLES, get_all_system_tables


class SystemDatabaseManager:
    """系统数据库管理器"""
    
    def __init__(self, database_manager: DatabaseManager):
        """
        初始化系统数据库管理器
        
        Args:
            database_manager: 数据库管理器实例
        """
        self.database_manager = database_manager
        self.logger = Logger()
        self.system_db_name = SYSTEM_DATABASE_NAME
    
    def ensure_system_database(self) -> bool:
        """
        确保系统数据库存在并正确初始化
        
        Returns:
            bool: 初始化是否成功
        """
        try:
            # 检查系统数据库是否存在
            exists = self.database_manager.database_exists(self.system_db_name)
            self.logger.info("SYSTEM_DB", 0, f"系统数据库 '{self.system_db_name}' 是否存在: {exists}")
            
            if not exists:
                self.logger.info("SYSTEM_DB", 0, f"System database '{self.system_db_name}' not found, creating...")
                
                # 创建系统数据库
                success = self._create_system_database()
                if not success:
                    raise RuntimeError("Failed to create system database")
                
                # 创建系统表
                success = self._create_system_tables()
                if not success:
                    raise RuntimeError("Failed to create system tables")
                
                # 初始化默认数据
                success = self._initialize_default_data()
                if not success:
                    raise RuntimeError("Failed to initialize default data")
                
                self.logger.info("SYSTEM_DB", 0, "System database created and initialized successfully")
                return True
            else:
                # 系统数据库已存在，验证表结构
                self.logger.info("SYSTEM_DB", 0, f"System database '{self.system_db_name}' found, validating...")
                
                success = self._validate_system_tables()
                if not success:
                    self.logger.warn("SYSTEM_DB", 0, "System tables validation failed, safely creating missing tables...")
                    # 安全地创建缺失表，不影响已有数据
                    missing_success = self._create_missing_tables()
                    if not missing_success:
                        self.logger.error("SYSTEM_DB", 0, "Failed to create missing tables, but continuing with existing tables")
                        # 即使创建缺失表失败，也继续使用已有的表
                else:
                    self.logger.info("SYSTEM_DB", 0, "All system tables are present and valid")
                
                # 额外检查：确保有默认用户数据
                if not self._validate_default_data():
                    self.logger.warn("SYSTEM_DB", 0, "Default data missing, reinitializing...")
                    reinit_success = self._initialize_default_data()
                    if not reinit_success:
                        self.logger.error("SYSTEM_DB", 0, "Failed to reinitialize default data")
                        return False
                else:
                    self.logger.info("SYSTEM_DB", 0, "Default data is present and valid")
                
                self.logger.info("SYSTEM_DB", 0, "System database validation completed")
                return True
                
        except Exception as e:
            self.logger.error("SYSTEM_DB", 0, f"Failed to ensure system database: {e}")
            return False
    
    def _create_system_database(self) -> bool:
        """创建系统数据库"""
        try:
            self.logger.info("SYSTEM_DB", 0, f"Creating system database '{self.system_db_name}'...")
            
            success = self.database_manager.create_database(
                self.system_db_name, 
                charset="utf8",
                collation="utf8_general_ci"
            )
            
            if success:
                self.logger.info("SYSTEM_DB", 0, f"Created system database '{self.system_db_name}'")
            else:
                self.logger.error("SYSTEM_DB", 0, f"Failed to create system database '{self.system_db_name}' - create_database returned False")
            
            return success
            
        except Exception as e:
            self.logger.error("SYSTEM_DB", 0, f"Failed to create system database: {e}")
            import traceback
            traceback.print_exc()
            return False
    
    def _create_system_tables(self) -> bool:
        """创建所有系统表"""
        try:
            self.logger.info("SYSTEM_DB", 0, f"Creating system tables...")
            
            system_db = self.database_manager.get_database(self.system_db_name)
            if not system_db:
                self.logger.warn("SYSTEM_DB", 0, f"无法获取系统数据库实例，尝试手动加载...")
                # 尝试手动触发延迟加载
                success = self.database_manager._load_database_instance(self.system_db_name)
                if success:
                    system_db = self.database_manager.get_database(self.system_db_name)
                    self.logger.info("SYSTEM_DB", 0, f"手动加载成功")
                else:
                    raise RuntimeError(f"Failed to load system database '{self.system_db_name}'")
            
            if not system_db:
                raise RuntimeError(f"System database '{self.system_db_name}' not found after all attempts")
            
            all_success = True
            system_tables = get_all_system_tables()
            
            self.logger.info("SYSTEM_DB", 0, f"System tables to create: {system_tables}")
            
            for table_name in system_tables:
                try:
                    table_schema = SYSTEM_TABLES[table_name]
                    self.logger.info("SYSTEM_DB", 0, f"Creating table '{table_name}' with {len(table_schema)} columns...")
                    
                    success = system_db.create_table(table_name, table_schema)
                    if success:
                        self.logger.info("SYSTEM_DB", 0, f"Created system table '{table_name}'")
                    else:
                        self.logger.error("SYSTEM_DB", 0, f"Failed to create system table '{table_name}' - create_table returned False")
                        all_success = False
                except Exception as table_error:
                    self.logger.error("SYSTEM_DB", 0, f"Exception creating table '{table_name}': {table_error}")
                    all_success = False
            
            return all_success
            
        except Exception as e:
            self.logger.error("SYSTEM_DB", 0, f"Failed to create system tables: {e}")
            import traceback
            traceback.print_exc()
            return False
    
    def _validate_system_tables(self) -> bool:
        """验证系统表是否存在"""
        try:
            self.logger.info("SYSTEM_DB", 0, f"开始验证系统表...")
            system_db = self.database_manager.get_database(self.system_db_name)
            if not system_db:
                self.logger.warn("SYSTEM_DB", 0, f"无法获取系统数据库实例，尝试手动加载...")
                # 尝试手动触发延迟加载
                success = self.database_manager._load_database_instance(self.system_db_name)
                if success:
                    system_db = self.database_manager.get_database(self.system_db_name)
                    self.logger.info("SYSTEM_DB", 0, f"手动加载成功")
                else:
                    self.logger.error("SYSTEM_DB", 0, f"手动加载失败")
                    return False
            
            if not system_db:
                self.logger.error("SYSTEM_DB", 0, f"所有方法都无法获取系统数据库实例")
                return False
            
            existing_tables = system_db.list_tables()
            required_tables = get_all_system_tables()
            
            self.logger.info("SYSTEM_DB", 0, f"现有表数量: {len(existing_tables)}")
            self.logger.info("SYSTEM_DB", 0, f"现有表列表: {existing_tables}")
            self.logger.info("SYSTEM_DB", 0, f"需要表数量: {len(required_tables)}")
            self.logger.info("SYSTEM_DB", 0, f"需要表列表: {required_tables}")
            
            missing_tables = [table for table in required_tables if table not in existing_tables]
            
            if missing_tables:
                self.logger.warn("SYSTEM_DB", 0, f"缺失的系统表: {missing_tables}")
                return False
            
            self.logger.info("SYSTEM_DB", 0, f"✅ 所有系统表都存在")
            return True
            
        except Exception as e:
            self.logger.error("SYSTEM_DB", 0, f"验证系统表失败: {e}")
            import traceback
            traceback.print_exc()
            return False
    
    def _create_missing_tables(self) -> bool:
        """创建缺失的系统表"""
        try:
            system_db = self.database_manager.get_database(self.system_db_name)
            if not system_db:
                self.logger.warn("SYSTEM_DB", 0, "无法获取系统数据库实例，尝试手动加载...")
                # 尝试手动触发延迟加载
                success = self.database_manager._load_database_instance(self.system_db_name)
                if success:
                    system_db = self.database_manager.get_database(self.system_db_name)
                    self.logger.info("SYSTEM_DB", 0, "手动加载成功")
                else:
                    self.logger.error("SYSTEM_DB", 0, "手动加载失败")
                    return False
            
            if not system_db:
                self.logger.error("SYSTEM_DB", 0, "所有方法都无法获取系统数据库实例")
                return False
            
            existing_tables = system_db.list_tables()
            required_tables = get_all_system_tables()
            
            self.logger.info("SYSTEM_DB", 0, f"Existing tables: {existing_tables}")
            self.logger.info("SYSTEM_DB", 0, f"Required tables: {required_tables}")
            
            missing_tables = [table for table in required_tables if table not in existing_tables]
            if not missing_tables:
                self.logger.info("SYSTEM_DB", 0, "No missing tables found")
                return True
            
            self.logger.info("SYSTEM_DB", 0, f"Creating missing tables: {missing_tables}")
            
            success_count = 0
            for table_name in missing_tables:
                try:
                    table_schema = SYSTEM_TABLES[table_name]
                    success = system_db.create_table(table_name, table_schema)
                    
                    if success:
                        self.logger.info("SYSTEM_DB", 0, f"✅ Created missing system table '{table_name}'")
                        success_count += 1
                    else:
                        self.logger.error("SYSTEM_DB", 0, f"❌ Failed to create missing system table '{table_name}'")
                except Exception as e:
                    self.logger.error("SYSTEM_DB", 0, f"❌ Exception creating table '{table_name}': {e}")
            
            # 如果至少创建了一些表，就认为是成功的
            if success_count > 0:
                self.logger.info("SYSTEM_DB", 0, f"Successfully created {success_count}/{len(missing_tables)} missing tables")
                return True
            else:
                self.logger.error("SYSTEM_DB", 0, f"Failed to create any missing tables (0/{len(missing_tables)})")
                return False
                        
        except Exception as e:
            self.logger.error("SYSTEM_DB", 0, f"Failed to create missing tables: {e}")
            return False
    
    def _validate_default_data(self) -> bool:
        """验证默认数据是否存在"""
        try:
            system_db = self.database_manager.get_database(self.system_db_name)
            if not system_db:
                self.logger.warn("SYSTEM_DB", 0, "无法获取系统数据库实例进行数据验证")
                return False
            
            # 检查是否有用户数据
            users = system_db.select_records("users")
            if len(users) == 0:
                self.logger.warn("SYSTEM_DB", 0, "users表为空，需要重新初始化默认数据")
                return False
            
            # 检查是否有root用户
            root_users = [user for user in users if user.get('username') == 'root']
            if len(root_users) == 0:
                self.logger.warn("SYSTEM_DB", 0, "没有找到root用户，需要重新初始化默认数据")
                return False
            
            self.logger.info("SYSTEM_DB", 0, f"默认数据验证通过，共有 {len(users)} 个用户，包括root用户")
            return True
            
        except Exception as e:
            self.logger.error("SYSTEM_DB", 0, f"验证默认数据失败: {e}")
            return False
    
    def _initialize_default_data(self) -> bool:
        """初始化默认数据"""
        try:
            # 这里会调用UserManagement模块初始化默认用户和权限
            # 但为了避免循环依赖，我们返回True，让上层调用者处理
            self.logger.info("SYSTEM_DB", 0, "System database structure ready for default data initialization")
            return True
            
        except Exception as e:
            self.logger.error("SYSTEM_DB", 0, f"Failed to initialize default data: {e}")
            return False
    
    def get_system_database(self) -> DatabaseInstance:
        """
        获取系统数据库实例
        
        Returns:
            DatabaseInstance: 系统数据库实例
        """
        return self.database_manager.get_database(self.system_db_name)
    
    def backup_system_database(self, backup_path: str) -> bool:
        """
        备份系统数据库
        
        Args:
            backup_path: 备份路径
            
        Returns:
            bool: 备份是否成功
        """
        try:
            # 实现真正的备份功能
            import shutil
            import json
            from datetime import datetime
            
            # 创建备份目录
            backup_dir = os.path.dirname(backup_path)
            if backup_dir and not os.path.exists(backup_dir):
                os.makedirs(backup_dir)
            
            # 获取系统数据库实例
            system_db = self.database_manager.get_database("system")
            if not system_db:
                self.logger.error("SYSTEM_DB", 0, "System database not found for backup")
                return False
            
            # 1. 备份系统表数据
            backup_data = {
                "backup_timestamp": datetime.now().isoformat(),
                "backup_version": "1.0",
                "tables": {}
            }
            
            # 备份系统用户表
            try:
                users_data = system_db.storage_engine.select_records("system_users")
                backup_data["tables"]["system_users"] = users_data or []
                self.logger.info("SYSTEM_DB", 0, f"Backed up {len(backup_data['tables']['system_users'])} users")
            except Exception as e:
                self.logger.warn("SYSTEM_DB", 0, f"Failed to backup users table: {e}")
                backup_data["tables"]["system_users"] = []
            
            # 备份系统权限表
            try:
                permissions_data = system_db.storage_engine.select_records("system_user_permissions")
                backup_data["tables"]["system_user_permissions"] = permissions_data or []
                self.logger.info("SYSTEM_DB", 0, f"Backed up {len(backup_data['tables']['system_user_permissions'])} permissions")
            except Exception as e:
                self.logger.warn("SYSTEM_DB", 0, f"Failed to backup permissions table: {e}")
                backup_data["tables"]["system_user_permissions"] = []
            
            # 备份用户会话表
            try:
                sessions_data = system_db.storage_engine.select_records("system_user_sessions")
                backup_data["tables"]["system_user_sessions"] = sessions_data or []
                self.logger.info("SYSTEM_DB", 0, f"Backed up {len(backup_data['tables']['system_user_sessions'])} sessions")
            except Exception as e:
                self.logger.warn("SYSTEM_DB", 0, f"Failed to backup sessions table: {e}")
                backup_data["tables"]["system_user_sessions"] = []
            
            # 2. 保存备份数据到JSON文件
            with open(backup_path, 'w', encoding='utf-8') as f:
                json.dump(backup_data, f, indent=2, ensure_ascii=False, default=str)
            
            # 3. 如果有原始数据文件，也备份一份
            system_db_path = os.path.join(self.database_manager.data_root, "system")
            if os.path.exists(system_db_path):
                # 备份原始数据库文件
                raw_backup_path = backup_path + ".raw"
                try:
                    if os.path.isdir(system_db_path):
                        shutil.copytree(system_db_path, raw_backup_path, dirs_exist_ok=True)
                    else:
                        shutil.copy2(system_db_path, raw_backup_path)
                    self.logger.info("SYSTEM_DB", 0, f"Raw database files backed up to {raw_backup_path}")
                except Exception as e:
                    self.logger.warn("SYSTEM_DB", 0, f"Failed to backup raw files: {e}")
            
            self.logger.info("SYSTEM_DB", 0, f"System database backup completed to {backup_path}")
            return True
            
        except Exception as e:
            self.logger.error("SYSTEM_DB", 0, f"Failed to backup system database: {e}")
            return False
    
    def get_system_info(self) -> Dict[str, Any]:
        """
        获取系统数据库信息
        
        Returns:
            Dict[str, Any]: 系统信息
        """
        try:
            system_db = self.get_system_database()
            if not system_db:
                return {"status": "not_available"}
            
            tables = system_db.list_tables()
            table_info = {}
            
            for table_name in tables:
                try:
                    schema = system_db.get_table_schema(table_name)
                    if schema:
                        table_info[table_name] = {
                            "columns": len(schema.columns),
                            "created_time": schema.created_time.isoformat() if hasattr(schema, 'created_time') else None
                        }
                except Exception:
                    table_info[table_name] = {"status": "error"}
            
            return {
                "status": "active",
                "database_name": self.system_db_name,
                "tables": table_info,
                "total_tables": len(tables)
            }
            
        except Exception as e:
            self.logger.error("SYSTEM_DB", 0, f"Failed to get system info: {e}")
            return {"status": "error", "error": str(e)}
    
    def cleanup_old_audit_logs(self, days_to_keep: int = 30) -> int:
        """
        清理旧的审计日志
        
        Args:
            days_to_keep: 保留天数
            
        Returns:
            int: 删除的记录数
        """
        try:
            system_db = self.get_system_database()
            if not system_db:
                return 0
            
            from datetime import datetime, timedelta
            cutoff_date = datetime.now() - timedelta(days=days_to_keep)
            
            condition = {"operation_time": {"$lt": cutoff_date}}
            deleted_count = system_db.delete_records("audit_log", condition)
            
            if deleted_count > 0:
                self.logger.info("SYSTEM_DB", 0, f"Cleaned up {deleted_count} old audit log records")
            
            return deleted_count
            
        except Exception as e:
            self.logger.error("SYSTEM_DB", 0, f"Failed to cleanup audit logs: {e}")
            return 0
